/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package rie07;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import sun.rmi.server.Dispatcher;

/**
 *
 * @author delpech.j
 */
public class Bdd {
    
        //base de donnees : javaweb, utilisateur : javaweb, mot de passe : javaweb
    private String madb="javaweb", user="javaweb", mdp="javaweb";
    private Connection connect = null;
    private PreparedStatement preparedStatement=null;
    private Statement statement;
    private ResultSet resultSet;
    
    
    //Connexion a la base de donnees derby
    public void connect(){
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {        
            
            connect = DriverManager.getConnection("jdbc:derby://localhost:1527/"+madb+";user="+user+";password="+mdp);
            System.out.println("Connexion OK");
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
    
/************** AJOUT DANS LA BDD ****************************/
    //methode d'ajout de la mmoire utilisee, libre, totale dans la base
     public void addMemoryBDD(int f, int u, int t){
        String serv = "rmiserver";
        int id=makeId("memoire", "id_mem");
        
        connect();
        //insertion du resultat dans la table memoire
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into "+madb+".memoire (id_mem,freemem, usedmem, totalmem) values ("+id+","+f+","+u+","+t+")");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
                close();
        } 
        int id_methode = makeId("methode", "id_methode");
        connect();
        //insertion de la methode getFreeMemory dans la table methode, lie a la table memoire
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into "+madb+".methode (id_methode, rmiserver, methode, id_mem, id_cpu, id_tache) values ('"+id_methode+"','"+serv+"','getFreeMemory',"+id+","+0+","+0+")");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
                close();
        } 

    }
    
     //methode d'ajout dans la base, pourcentage de CPU
    public void addCpuBDD(String cpu){
        String serv = "rmiserver";
        int id=makeId("cpu", "id_cpu");

        connect();
        //insertion du resultat dans la table cpu
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into "+madb+".cpu (id_cpu,cpu) values ("+id+",'"+cpu+"')");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
            close();
        } 
            
        int id_methode = makeId("methode", "id_methode");
        connect();
        //insertion de la methode getCPU dans la table methode, lie a la table CPU
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into "+madb+".methode (id_methode, rmiserver, methode, id_mem, id_cpu, id_tache) values ('"+id_methode+"','"+serv+"','getCpu',"+0+","+id+","+0+")");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
            close();
        } 

    }
     
    //methode d'ajout de taches dans la base
    public void addTaskBDD(String serv, List<String> temp){
        int id=makeId("tache", "id_tache");
        
        connect();
        //insertion du resultat dans la table tache
        try {
            for(int i=0; i<temp.size();i++){
                preparedStatement = connect.prepareStatement(
                        "insert into "+madb+".tache (id_tache, tache) values ("+id+","+temp.get(i)+")");
                preparedStatement.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
                close();
        } 

        int id_methode = makeId("methode", "id_methode");
        connect();
        //insertion de la methode getTaskList dans la table methode, lie a la table tache
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into "+madb+".methode (id_methode, rmiserver, methode, id_mem, id_cpu, id_tache) values ('"+id_methode+"','"+serv+"','getTaskList',"+0+","+0+","+id+")");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
                close();
        } 

    } 
    
/************** SUPPRESSION DU CONTENU DE LA BDD ****************************/    
    //suppression de toutes les entrées existantes dans la base de données
    public void deleteHistory(){
        connect();
        try {
            preparedStatement = connect.prepareStatement(
                    "delete from "+madb+".tache");
            preparedStatement.executeUpdate();
            
            preparedStatement = connect.prepareStatement(
                    "delete from "+madb+".cpu");
            preparedStatement.executeUpdate();
            
            preparedStatement = connect.prepareStatement(
                    "delete from "+madb+".memoire");
            preparedStatement.executeUpdate();
            
            preparedStatement = connect.prepareStatement(
                    "delete from "+madb+".methode");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }finally {
                close();
        } 
    }
     
/************** SELECTION DANS LA BDD ****************************/      
    //Historique
    public Methode getMethode(){
         Methode mamethode = new Methode();
        //ArrayList<String> meth = new ArrayList<String>();
        try {
                connect();
                statement = connect.createStatement();
                resultSet = statement.executeQuery("select * from "+madb+".methode");
                System.out.println("Coucou");
                while(resultSet.next()){
                    System.out.println("aie");
                    mamethode.setId_methode(resultSet.getString("id_methode"));
                    mamethode.setServer(resultSet.getString("rmiserver"));
                    mamethode.setMethode(resultSet.getString("methode"));
                    if(resultSet.getInt("id_mem")!=0)
                        mamethode.setId(resultSet.getString("id_mem"));
                    if(resultSet.getInt("id_cpu")!=0)
                        mamethode.setId(resultSet.getString("id_cpu"));
                    if(resultSet.getInt("id_tache")!=0)
                        mamethode.setId(resultSet.getString("id_tache"));
                    //if(resultSet.getInt("id_methode")<0)
                    System.out.println("bite");
                    }
            } catch (Exception ex) {
                System.out.println("Erreur BDD");
            }
        return mamethode;
        
    }
    
    
//    select id_methode from methode
//              stock variable id_mem, id_cpu, id_task
//                      if(id_mem!=0)
//                        select free, used, totale from memoire where id_mem.mem = id_mem
//                                arraylist.add(free); arraylist.add(used); arraylist.add(total);
//                      if(id_cpu!=0) ...
//                      if(id_list!=0) ...    
                      
                      
//Verifier si l'id existe dans la table, renvoi vrai s'il existe    
    public boolean checkIdExists(int monid, String matable, String monchamp)throws Exception{
            try {
                    connect();
                    statement = connect.createStatement();
                    resultSet = statement
              .executeQuery("select * from "+madb+"."+matable+" where "+monchamp+"="+monid+"");
                    while (resultSet.next()) {
                            int test = resultSet.getInt(monchamp);

                            if(monid==test){
                                    return true;
                            }
                    }  
            } catch (Exception e) {
                    throw e;
            } finally {
                    close();
            } 
            return false;
    }

//Fonction de creation de l'id memoire/cpu/tache
    public int makeId(String matable, String monchamp){
        //on commence a 1, car dans methode, l'id 0 correspond a "non utilise"
        int id=1;
        
        //tant que l'id existe, on incremente de 1
        while(true){
            try {
                if(checkIdExists(id, matable, monchamp)){
                    id++;	
                }
                else{
                    break;
                }
            } catch (Exception ex) {
                
            }
        }
        return id;
    }
    
    //fonction qui ferment les connexions ouverte avec la base de données
    private void close() {
	  		try {
	  			if (resultSet != null) {
	  				resultSet.close();
	  			}
	  			if (statement != null) {
	  				statement.close();
	  			}
	
	  			if (connect != null) {
	  				connect.close();
	  			}
	  			
	  			if (preparedStatement != null) {
	  				preparedStatement.close();
	  			}
	  		} catch (Exception e) {
	
	  		}
		}

    
}
